package com.wpf.dao;

import com.wpf.bean.Userbean;
import com.wpf.utils.JDBCUtils;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

public class UserDAO {
	    //标题栏的输出
	    public static void bufferAppend (StringBuffer buffer) {
	    	buffer.append("<table class=\"data_table\">");
	    	buffer.append("<tr>");
	    	buffer.append("<th>学号</th>");
	    	buffer.append("<th>姓名</th>");
	    	buffer.append("<th>邮箱</th>");
	    	buffer.append("<th>出生日期</th>");
	    	buffer.append("<th colspan='2'>操作</th>");
	    	buffer.append("</tr>");
	    }
	    //数据的输出
	    public static void bufferAppend (StringBuffer buffer ,ArrayList<Userbean> list,int i) {
	    	if(i%2==0) {
	    		buffer.append("<tr class=\"even\" >");
	    	}else {
	    		buffer.append("<tr class=\"odd\" >");
	    	}
	    	buffer.append("<td>"+list.get(i).getId()+"</td>");
	    	buffer.append("<td>"+list.get(i).getUsername()+"</td>");
	    	buffer.append("<td>"+list.get(i).getEmail()+"</td>");
	    	buffer.append("<td>"+list.get(i).getBirthday()+"</td>");
	    	buffer.append("<td><a class=\"a_edit\" href='wpf_QueryByID?id="+list.get(i).getId()+"'>编辑</a></td>");
	    	buffer.append("<td><a class=\"a_dele\" onclick='return confirm(\"确定要删除吗?\")' href='wpf_DeleteData?id="
	    								+list.get(i).getId()+"&username="+list.get(i).getUsername()+"'>删除</a></td>");
	    	buffer.append("</tr>");
		}
		//添加用户
		public boolean insert(Userbean user) {
			Connection conn = null;
			Statement stmt = null;
			ResultSet rs = null;
			try {
			//获得数据库连接
			conn = JDBCUtils.getConnection();
			// 获得Statement对象
			stmt = (Statement) conn.createStatement();
			// 发送SQL语句
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
			String birthday = sdf.format(new Date());
			if(user.getBirthday()!=null) {
				birthday = sdf.format(user.getBirthday());
			}
		    String sql = "INSERT INTO users(id,username,password,email,birthday) "+
						"VALUES("
						+ user.getId()
						+ ",'"
						+ user.getUsername()
						+ "','"
						+ user.getPassword()
						+ "','"
						+ user.getEmail()
						+ "','"
						+ birthday + "')";
				int num = stmt.executeUpdate(sql);
				if (num > 0) {
					return true;
				}
				return false;
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				JDBCUtils.release(rs, stmt, conn);
			}
			return false;
		}
		//查询所有User对象
			public ArrayList<Userbean> findAll(String key,String sort) {
				Connection conn = null;
				Statement stmt = null;
				ResultSet rs = null;
				ArrayList<Userbean> list = new ArrayList<Userbean>();
				try {
					//获得数据库连接
					conn = JDBCUtils.getConnection();
					// 获得Statement对象
					stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
					// 发送SQL语句
					String sql = "SELECT * FROM users ORDER BY "+key+" "+sort;
					rs = stmt.executeQuery(sql);
					// 处理结果集
					while (rs.next()) {
						Userbean user = new Userbean();
						user.setId(rs.getInt("id"));
						user.setUsername(rs.getString("username"));
						user.setPassword(rs.getString("password"));
						user.setEmail(rs.getString("email"));
						user.setBirthday(rs.getDate("birthday"));
						list.add(user);
					}
					return list;
				} catch (Exception e) {
					e.printStackTrace();
				} finally {
					JDBCUtils.release(rs, stmt, conn);
				}
				return null;
			}
		//根据指定id查找用户
		public Userbean find(int id) {
			Connection conn = null;
			Statement stmt = null;
			ResultSet rs = null;
			try {
				//获得数据库连接
				conn = JDBCUtils.getConnection();
				// 获得Statement对象
				stmt = conn.createStatement();
				// 发送SQL语句
				String sql = "SELECT * FROM users WHERE id=" + id;
				rs = stmt.executeQuery(sql);
				// 处理结果集
				while (rs.next()) {
					Userbean user = new Userbean();
					user.setId(rs.getInt("id"));
					user.setUsername(rs.getString("username"));
					user.setPassword(rs.getString("password"));
					user.setEmail(rs.getString("email"));
					user.setBirthday(rs.getDate("birthday"));
					return user;
					}
					return null;
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				JDBCUtils.release(rs, stmt, conn);
			}
			return null;
		}
		//根据条件查找用户
		public ArrayList<Userbean> findbykey(String key,int way,String content,String sort) {
			Connection conn = null;
			Statement stmt = null;
			ResultSet rs = null;
			ArrayList<Userbean> list = new ArrayList<Userbean>();
			try {
				//获得数据库连接
				conn = JDBCUtils.getConnection();
				// 获得Statement对象
				stmt = conn.createStatement();
				// 发送SQL语句
				String sql = "SELECT * FROM users WHERE "+key;
				String sortsql = " ORDER BY "+key+" "+sort;
				switch (way) {
					case 0: {
						sql = sql+"='"+content+"'"+sortsql;
						break;
					}
					case 1: {
						sql = sql+" LIKE '%" + content + "%' "+sortsql;
						break;
					}
					case 2: {
						sql = sql+" LIKE '" + content + "%' "+sortsql;
						break;
					}
					case 3: {
						sql = sql+" LIKE '%" + content + "' "+sortsql;
						break;
					}
				}
				rs = stmt.executeQuery(sql);
				// 处理结果集
				while (rs.next()) {
					Userbean user = new Userbean();
					user.setId(rs.getInt("id"));
					user.setUsername(rs.getString("username"));
					user.setPassword(rs.getString("password"));
					user.setEmail(rs.getString("email"));
					user.setBirthday(rs.getDate("birthday"));
					list.add(user);
				}
				return list;
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				JDBCUtils.release(rs, stmt, conn);
			}
			return null;
		}
		//删除用户
		public boolean delete(int id) {
			Connection conn = null;
			Statement stmt = null;
			ResultSet rs = null;
			try {
				//获得数据库连接
				conn = JDBCUtils.getConnection();
				// 获得Statement对象
				stmt = conn.createStatement();
				// 发送SQL语句
				String sql = "DELETE FROM users WHERE id=" + id;
				int num = stmt.executeUpdate(sql);
				if (num > 0) {
					return true;
				}
				return false;
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				JDBCUtils.release(rs, stmt, conn);
			}
			return false;
		}
		//修改用户
		public boolean update(Userbean user) {
			Connection conn = null;
			Statement stmt = null;
			ResultSet rs = null;
			try {
				//获得数据库连接
				conn = JDBCUtils.getConnection();
				// 获得Statement对象
				stmt = conn.createStatement();
				// 发送SQL语句
				SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
				String birthday = sdf.format(new Date());
				if(user.getBirthday()!=null) {
					birthday = sdf.format(user.getBirthday());
				}
				String sql = "UPDATE users set username='" + user.getUsername()
						+ "',password='" + user.getPassword() + "',email='"
						+ user.getEmail() + "',birthday='" + birthday
						+ "' WHERE id=" + user.getId();
				int num = stmt.executeUpdate(sql);
				if (num > 0) {
					return true;
				}
				return false;
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				JDBCUtils.release(rs, stmt, conn);
			}
			return false;
		}
		//分页数据记录
		public int pageMess(ArrayList<Userbean> list,int dbcount,int pagesize) {
			//页数
			int pageCount = 0;
			if(dbcount!=0&&pagesize!=1) {
				if(dbcount%pagesize==0) {
					pageCount =  dbcount/pagesize;
				}else if(dbcount%pagesize!=0){
					pageCount =  dbcount/pagesize + 1;
				}
				return pageCount;
			}
			return 1;
		}
		//加密算法
	    /***
	     * 使用凯撒加密方式加密数据
	     * @param orignal 原文
	     * @param key 密钥
	     * @return 加密后的字符
	     */
	    public static String encryptKaisa(String orignal, int key) {
	        //将字符串转换为数组
	        char[] chars = orignal.toCharArray();
	        StringBuffer buffer = new StringBuffer();
	        //遍历数组
	        for(char aChar : chars) {
	            //获取字符的ASCII编码
	            int asciiCode = aChar;
	            //偏移数据
	            asciiCode += key;
	            //将偏移后的数据转为字符
	            char result = (char)asciiCode;
	            //拼接数据
	            buffer.append(result);
	        }
	        return buffer.toString();
	    }
	    
	    /**
	     * 使用凯撒加密方式解密数据
	     *
	     * @param encryptedData :密文
	     * @param key           :密钥
	     * @return : 源数据
	     */
	    public static String decryptKaiser(String encryptedData, int key) {
	        // 将字符串转为字符数组
	        char[] chars = encryptedData.toCharArray();
	        StringBuilder sb = new StringBuilder();
	        // 遍历数组
	        for (char aChar : chars) {
	            // 获取字符的ASCII编码
	            int asciiCode = aChar;
	            // 偏移数据
	            asciiCode -= key;
	            // 将偏移后的数据转为字符
	            char result = (char) asciiCode;
	            // 拼接数据
	            sb.append(result);
	        }
	        return sb.toString();
	    }
}
